﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.Common;

namespace MM.Common.DBBase
{
    public class DbSqlData : DbBase
    {
        // Fields
        protected SqlConnection dataConn;
        protected SqlTransaction dbTransaction;

        // Methods
        public DbSqlData()
        {
            base.m_DataBaseType = DataBaseTypeEnum.SQLServer;
        }

        protected void AddParms(DbCommand cmd, DataParamCol parms)
        {
            foreach (DataParam param in parms)
            {
                if (param.Value == null)
                {
                    param.Value = DBNull.Value;
                }
                if ((param.Value.GetType().Name == "DateTime") && (((DateTime)param.Value) == DateTime.MinValue))
                {
                    param.Value = DBNull.Value;
                }
                if (param.Name[0] != '@')
                {
                    param.Name = "@" + param.Name;
                }
                string str = param.Name.Substring(1);
                cmd.CommandText = cmd.CommandText.Replace(":" + str, "@" + str);
                ((SqlCommand)cmd).Parameters.AddWithValue(param.Name, param.Value);
                cmd.Parameters[param.Name].Direction = param.Direction;
                if (param.Size != 0)
                {
                    cmd.Parameters[param.Name].Size = param.Size;
                }
                if (param.Type != null)
                {
                    ((SqlCommand)cmd).Parameters[param.Name].SqlDbType = this.GetDBType(param.Type);
                }
            }
        }

        protected override bool BeginTransaction()
        {
            base.ClearErrorMessage();
            try
            {
                this.dbTransaction = this.dataConn.BeginTransaction();
                return true;
            }
            catch (Exception exception)
            {
                base.errorMessage.Append(exception.Message);
                return false;
            }
        }

        protected override bool CommitTransaction()
        {
            base.ClearErrorMessage();
            try
            {
                if (this.IsExistTransaction)
                {
                    this.dbTransaction.Commit();
                }
                return true;
            }
            catch (Exception exception)
            {
                base.errorMessage.Append(exception.Message);
                return false;
            }
        }

        public override string DataSource()
        {
            return this.dataConn.DataSource;
        }

        public override void DBClose()
        {
            this.dataConn.Close();
        }

        public override bool ExecuteProcedure(string procedure_name, DataParamCol parms, bool commit)
        {
            bool flag = true;
            base.ClearErrorMessage();
            if (commit)
            {
                this.TransactionBegin();
            }
            SqlCommand cmd = new SqlCommand(procedure_name, this.dataConn, this.dbTransaction);
            try
            {
                cmd.CommandType = CommandType.StoredProcedure;
                if (parms != null)
                {
                    this.AddParms(cmd, parms);
                }
                cmd.ExecuteNonQuery();
                if (commit)
                {
                    base.TransactionCommit();
                }
                if (parms != null)
                {
                    base.GetParmsValue(cmd, parms);
                }
            }
            catch (Exception exception)
            {
                if (commit)
                {
                    base.TransactionRollback();
                }
                base.errorMessage.Append(exception.Message);
                flag = false;
            }
            return flag;
        }

        public override bool ExecuteScalar(string commandtext, DataParamCol parms, ref object obj, bool close_db)
        {
            bool flag = true;
            base.ClearErrorMessage();
            SqlCommand cmd = new SqlCommand(commandtext, this.dataConn, this.dbTransaction);
            try
            {
                if ((base.transactionID == -1) && !this.IsOpen)
                {
                    this.Open();
                }
                if (parms != null)
                {
                    this.AddParms(cmd, parms);
                }
                obj = cmd.ExecuteScalar();
            }
            catch (Exception exception)
            {
                flag = false;
                base.errorMessage.Append(exception.Message);
            }
            finally
            {
                if ((base.autoClose && close_db) && (this.dbTransaction == null))
                {
                    this.DBClose();
                }
            }
            return flag;
        }

        public override int ExecuteSql(string commandtext, DataParamCol parms, int nrows, bool commit)
        {
            int num;
            base.ClearErrorMessage();
            if (commit)
            {
                this.TransactionBegin();
            }
            SqlCommand cmd = new SqlCommand(commandtext, this.dataConn, this.dbTransaction);
            try
            {
                if (parms != null)
                {
                    this.AddParms(cmd, parms);
                }
                num = cmd.ExecuteNonQuery();
                if ((nrows > 0) && (num != nrows))
                {
                    throw new DataException("执行作用的记录数与要求不一致\r\n " + commandtext);
                }
                if (commit)
                {
                    base.TransactionCommit();
                }
            }
            catch (Exception exception)
            {
                if (commit)
                {
                    base.TransactionRollback();
                }
                base.errorMessage.Append(exception.Message);
                num = -1;
            }
            return num;
        }

        public override string GetDbDefault(string table_name, string column_name)
        {
            object obj2 = new object();
            string str = "";
            string commandtext = "SELECT COLUMN_DEFAULT FROM INFORMATION_SCHEMA.COLUMNS \r\n   WHERE TABLE_NAME = :tname AND COLUMN_NAME = :cname ";
            DataParamCol parms = new DataParamCol();
            parms.AddBatch(new object[] { "tname", table_name, "cname", column_name });
            if (base.ExecuteScalar(commandtext, parms, ref obj2))
            {
                str = obj2.ToString();
                if (str.Length >= 2)
                {
                    str = str.Substring(1, str.Length - 2);
                    if (str[0] == '\'')
                    {
                        str = str.Substring(1, str.Length - 2);
                    }
                }
            }
            return str;
        }

        public override DateTime GetDBTime()
        {
            SqlCommand command = new SqlCommand("SELECT GETDATE()", this.dataConn, this.dbTransaction);
            return (DateTime)command.ExecuteScalar();
        }

        protected SqlDbType GetDBType(string type)
        {
            switch (type)
            {
                case "System.String":
                    return SqlDbType.VarChar;

                case "System.Int32":
                    return SqlDbType.Int;

                case "System.Int16":
                    return SqlDbType.SmallInt;

                case "System.Single":
                case "System.Double":
                case "System.Decimal":
                    return SqlDbType.Decimal;

                case "System.DateTime":
                    return SqlDbType.DateTime;
            }
            throw new DataException("不支持的参数类型: " + type.ToString());
        }

        protected override bool Open()
        {
            base.ClearErrorMessage();
            try
            {
                this.dataConn.Open();
                return true;
            }
            catch (Exception exception)
            {
                base.errorMessage.Append(exception.Message);
                return false;
            }
        }

        public override bool Open(string connectstring)
        {
            this.dataConn = new SqlConnection();
            this.dataConn.ConnectionString = connectstring;
            return this.Open();
        }

        public override bool Retrieve(string commandtext, DataParamCol parm, ref DataTable dt, bool close_db)
        {
            bool flag = false;
            base.ClearErrorMessage();
            SqlCommand cmd = new SqlCommand(commandtext, this.dataConn, this.dbTransaction);
            try
            {
                if ((base.transactionID == -1) && !this.IsOpen)
                {
                    this.Open();
                }
                if (parm != null)
                {
                    this.AddParms(cmd, parm);
                }
                new SqlDataAdapter(cmd).Fill(dt);
                flag = true;
            }
            catch (Exception exception)
            {
                base.errorMessage.Append(exception.Message);
            }
            finally
            {
                if ((base.autoClose && close_db) && (this.dbTransaction == null))
                {
                    this.DBClose();
                }
            }
            return flag;
        }

        public override bool Retrieve(string[] commandtexts, DataParamCol[] parms, ref DataTable[] dts, bool close_db)
        {
            bool flag = false;
            base.ClearErrorMessage();
            try
            {
                if ((base.transactionID == -1) && !this.IsOpen)
                {
                    this.Open();
                }
                for (int i = 0; i < commandtexts.Length; i++)
                {
                    SqlCommand cmd = new SqlCommand(commandtexts[i], this.dataConn, this.dbTransaction);
                    if (parms[i] != null)
                    {
                        this.AddParms(cmd, parms[i]);
                    }
                    new SqlDataAdapter(cmd).Fill(dts[i]);
                }
                flag = true;
            }
            catch (Exception exception)
            {
                base.errorMessage.Append(exception.Message);
            }
            finally
            {
                if ((base.autoClose && close_db) && (this.dbTransaction == null))
                {
                    this.DBClose();
                }
            }
            return flag;
        }

        public override bool RetrieveProcedure(string procedure_name, DataParamCol parms, ref DataTable dt, bool close_db)
        {
            bool flag = true;
            base.ClearErrorMessage();
            SqlCommand selectCommand = new SqlCommand(procedure_name, this.dataConn);
            SqlDataAdapter adapter = new SqlDataAdapter(selectCommand);
            try
            {
                if ((base.transactionID == -1) && !this.IsOpen)
                {
                    this.Open();
                }
                selectCommand.CommandType = CommandType.StoredProcedure;
                if (parms != null)
                {
                    this.AddParms(selectCommand, parms);
                }
                if (dt == null)
                {
                    dt = new DataTable();
                }
                adapter.Fill(dt);
                if (parms != null)
                {
                    base.GetParmsValue(selectCommand, parms);
                }
            }
            catch (Exception exception)
            {
                base.errorMessage.Append(exception.Message);
                flag = false;
            }
            finally
            {
                if ((base.autoClose && close_db) && (this.dbTransaction == null))
                {
                    this.DBClose();
                }
            }
            return flag;
        }

        protected override bool RollbackTransaction()
        {
            base.ClearErrorMessage();
            try
            {
                if (this.IsExistTransaction)
                {
                    this.dbTransaction.Rollback();
                }
                return true;
            }
            catch (Exception exception)
            {
                base.errorMessage.Append(exception.Message);
                return false;
            }
        }

        public override int RunSql(string[] commandtexts, DataParamCol[] parms, int[] nrows, bool commit)
        {
            int num = 0;
            base.ClearErrorMessage();
            if (commit)
            {
                this.TransactionBegin();
            }
            SqlCommand cmd = new SqlCommand
            {
                Connection = this.dataConn,
                Transaction = this.dbTransaction
            };
            try
            {
                for (int i = 0; i < commandtexts.Length; i++)
                {
                    num = i + 1;
                    cmd.CommandText = commandtexts[i];
                    if ((parms != null) && (parms[i] != null))
                    {
                        this.AddParms(cmd, parms[i]);
                    }
                    int num2 = cmd.ExecuteNonQuery();
                    if ((nrows[i] > 0) && (num2 != nrows[i]))
                    {
                        throw new DataException("执行作用的记录数与要求不一致\r\n " + commandtexts[i]);
                    }
                    cmd.Parameters.Clear();
                }
                if (commit)
                {
                    base.TransactionCommit();
                }
                num = 0;
            }
            catch (Exception exception)
            {
                if (commit)
                {
                    base.TransactionRollback();
                }
                base.errorMessage.Append(exception.Message);
                base.errorMessage.Append("\r\n");
                base.errorMessage.Append(cmd.CommandText);
            }
            return num;
        }

        // Properties
        public override bool IsExistTransaction
        {
            get
            {
                return ((this.dbTransaction != null) && (this.dbTransaction.Connection != null));
            }
        }

        public override bool IsOpen
        {
            get
            {
                return ((this.dataConn != null) && (this.dataConn.State == ConnectionState.Open));
            }
        }
    }
}
